import warnings
warnings.filterwarnings("ignore")
import pandas as pd
pd.set_option('display.max_columns', None)
pd.set_option('display.max_rows', 20)
pd.set_option('display.float_format', lambda x: str(x))
from Models import *
from sqlalchemy import *
from utils.utils import exec_orm_sql
engine = create_engine('mysql+pymysql://')



# # 构建查询
# standard_aquirer_name = '安信价值精选股票型证券投资基金'
# query = select(
#     func.count()
# ).select_from(LC_LegalDistribution).where(
#     LC_LegalDistribution.AquirerName == standard_aquirer_name
# ).where(
#     func.date(LC_LegalDistribution.InitialInfoPublDate).between('2019-01-01', '2019-12-31')
# )
#
# # 执行查询并打印
# print(f"查询标准收购人名称为 '{standard_aquirer_name}' 的法律分配记录数开始")
# result = exec_orm_sql(query.compile(engine, compile_kwargs={"literal_binds": True}))
# print(f"查询标准收购人名称为 '{standard_aquirer_name}' 的法律分配记录数结束")

#
# query = select(
#     func.count(func.distinct(LC_SMAttendInfo.CompanyCode)).label('CompanyCount')
# ).where(
#     (LC_SMAttendInfo.TestmonyLawOffice == '上海市锦天城律师事务所') &
#     (LC_SMAttendInfo.MeetingType == 1) &
#     (LC_SMAttendInfo.Year == 2020) &
#     (LC_SMAttendInfo.IfEffected == 1)
# )
#
# print("查询2020年上海市锦天城律师事务所见证的年度股东大会公司数量开始")
# result = exec_orm_sql(query.compile(engine, compile_kwargs={"literal_binds": True}))
# print("查询2020年上海市锦天城律师事务所见证的年度股东大会公司数量结束")
#
# if not result.empty:
#     count = result.iloc[0]['CompanyCount']
#     print(f"【答案: {count}】")
# else:
#     print("【答案: 0】")
#
#
#

# # 定义公司的CompanyCode
# company_code = 73222
#
# # 构建查询，计算回购总金额
# query = select(
#     func.round(func.sum(func.coalesce(LC_Buyback.BuybackMoney, 0)), 1).label('TotalBuybackMoney')
# ).where(
#     LC_Buyback.CompanyCode == company_code,
#     func.date(LC_Buyback.EndDate) <= '2021-12-31'
# )
#
# # 查询并判断结果
# print("查询岩山科技截至2021年12月31日的股份回购总金额开始")
# result = exec_orm_sql(query.compile(engine, compile_kwargs={"literal_binds": True}))
# print("查询岩山科技截至2021年12月31日的股份回购总金额结束")
#
# if not result.empty:
#     total_buyback_money = result['TotalBuybackMoney'].iloc[0]
#     print(f"【答案: 岩山科技截至2021年12月31日的股份回购总金额为{total_buyback_money:.1f}元】")
# else:
#     print("【答案: 岩山科技截至2021年12月31日没有进行股份回购】")


# company_code = 72579
#
# # 查询2019年最新的前十大股东名单
# subquery = select(
#     func.max(LC_MainSHListNew.EndDate)
# ).where(
#     (LC_MainSHListNew.CompanyCode == company_code) &
#     (func.year(cast(LC_MainSHListNew.EndDate, Date)) == 2019) &
#     (LC_MainSHListNew.InfoTypeCode == 1)
# ).scalar_subquery()
#
# # 构建主查询
# query = select(
#     LC_MainSHListNew.SHList
# ).where(
#     (LC_MainSHListNew.CompanyCode == company_code) &
#     (func.year(cast(LC_MainSHListNew.EndDate, Date)) == 2019) &
#     (LC_MainSHListNew.InfoTypeCode == 1) &
#     (LC_MainSHListNew.EndDate == subquery)
# )
#
# print("查询海油发展2019年最新的前十大股东名单开始")
# result = exec_orm_sql(query.compile(engine, compile_kwargs={"literal_binds": True}))
# print("查询海油发展2019年最新的前十大股东名单结束")
#
# if not result.empty:
#     # 统计主要从事'银行'相关业务的股东数量
#     bank_related_count = len([i for i in result['SHList'] if '银行' in str(i)])
#     print(f"【答案:{bank_related_count}】")
# else:
#     print("【答案:0】")
#
#
# 公司代码
company_code = 72579

# 构建查询语句，查询2019年海油发展的有效股东大会次数
query = select(
    func.count().label('MeetingCount')
).where(
    (LC_SMAttendInfo.CompanyCode == company_code) &
    (LC_SMAttendInfo.Year== 2019) &
    (LC_SMAttendInfo.IfEffected == 1)
)

print("查询海油发展2019年召开股东大会次数开始")
result = exec_orm_sql(query.compile(engine, compile_kwargs={"literal_binds": True}))
print("查询海油发展2019年召开股东大会次数结束")

# 输出答案
if not result.empty:
    meeting_count = result.iloc[0]['MeetingCount']
    print(f"【答案:{meeting_count}】")
else:
    print("【答案:0】")



